package com.feng.web.controller;

import com.alibaba.fastjson.JSONObject;
import com.feng.model.AdminUser;
import com.feng.model.Bill;
import com.feng.model.Count;
import com.feng.model.WrongBill;
import com.feng.service.BillService;
import com.feng.service.WrongBillService;
import com.feng.util.ExcelReader;
import com.feng.util.PageResult;
import com.feng.util.Search;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.Iterator;

@RestController
@RequestMapping(value = "/bill")
public class BillAction {
	private static Logger log = LogManager.getLogger(BillAction.class);

    public final static String BUSINESS_NO = "商业保单号";
    public final static String COMPULSORY_NO = "交强保单号";
    public final static String BUSINESS_AMOUNT = "商业险";
    public final static String BUSINESS_AMOUNT2 = "商业";
    public final static String COMPULSORY_AMOUNT = "交强险";
    public final static String COMPULSORY_AMOUNT2 = "交强";
    public final static String LICENSE_PLATE = "车牌";
    public final static String NAME = "被保险人";
    public final static String COMPANY_NAME = "保险公司";
    private final static String TEMP_UPLOAD_PATH = "/temp";


	@Autowired
    private BillService billService;

    @Autowired
    private WrongBillService wrongBillService;
    
    @RequestMapping(value = "/list", method = RequestMethod.POST)
    public PageResult<Bill> list(@RequestBody Search search,HttpSession session) {
        AdminUser user = (AdminUser)session.getAttribute("admin");
        PageResult<Bill> set = null;
        try {
            set = billService.findByPage(search,user);
        } catch (Exception e) {
            e.printStackTrace();
            log.error(e.getMessage());
        }
        return set;
    }

    @RequestMapping(value = "/listWrongBill", method = RequestMethod.POST)
    public PageResult<WrongBill> listWrongBill(@RequestBody Search search) {
        PageResult<WrongBill> set = null;
        try {
            set = wrongBillService.findByPage(search);
        } catch (Exception e) {
            e.printStackTrace();
            log.error(e.getMessage());
        }
        return set;
    }

    // 通过Id查账管理员
    @RequestMapping(value = "/get/{id}", method = RequestMethod.GET)
    public JSONObject findById(@PathVariable("id") Long id) {
        JSONObject ret = new JSONObject();
        boolean success = false;
        try {
            Bill agent = billService.get(id);
            ret.put("agent", agent);
            success = true;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
        }
        ret.put("success", success);
        return ret;
    }
    // 通过Id查账管理员
    @RequestMapping(value = "/getByInsuranceNo", method = RequestMethod.GET)
    public JSONObject getByInsuranceNo(@RequestParam("insuranceNo") String insuranceNo) {
        JSONObject ret = new JSONObject();
        boolean success = false;
        try {
            Bill bill = billService.getByInsuranceNo(insuranceNo);
            ret.put("bill", bill);
            success = true;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
        }
        ret.put("success", success);
        return ret;
    }

    //判断球员字段的唯一性
    @RequestMapping(value = "/checkUnique", method = RequestMethod.POST)
    public JSONObject checkUnique(@RequestParam(value="id",required = false) Long id,
                                  @RequestParam(value="insuranceNo",required = false) String insuranceNo){
        JSONObject ret = new JSONObject();
        Bill bill = null ;
        if(insuranceNo!=null){
            bill  = billService.getByInsuranceNo(insuranceNo);
            if(id!=null && bill!=null && id.equals(bill.getId())){
                bill = null;
            }
        }
        if(bill == null){
            ret.put("isUnique", true);
        }else{
            ret.put("isUnique", false);
        }
        return ret;
    }

    // 保存或更新
    @RequestMapping(value = "/saveOrUpdate", method = RequestMethod.POST)
    public JSONObject saveOrUpdate(@RequestBody Bill bill, HttpSession session) {
        JSONObject ret = new JSONObject();
        boolean success = false;
        try {
            bill.setOptime(new Date());
            billService.saveOrUpdate(bill);
            ret.put("bill",bill);
            success = true;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
        }
        ret.put("success", success);
        return ret;
    }

    // 通过id删除
    @RequestMapping(value = "/del/{billId}", method = RequestMethod.GET)
    public JSONObject delByClass(@PathVariable("billId") final Long billId,
            HttpSession session) {
        JSONObject ret = new JSONObject();
        boolean success = false;
        try {
            billService.del(billId);
            success = true;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
        }
        ret.put("success", success);
        return ret;
    }

    // 通过id删除
    @RequestMapping(value = "/delWrong/{billId}", method = RequestMethod.GET)
    public JSONObject delWrong(@PathVariable("billId") final Long billId,
                                 HttpSession session) {
        JSONObject ret = new JSONObject();
        boolean success = false;
        try {
            wrongBillService.del(billId);
            success = true;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
        }
        ret.put("success", success);
        return ret;
    }

    // 通过id删除
    @RequestMapping(value = "/truncate", method = RequestMethod.GET)
    public JSONObject truncate(HttpSession session) {
        JSONObject ret = new JSONObject();
        boolean success = false;
        AdminUser admin = (AdminUser)session.getAttribute("admin");
        if(admin == null){
            ret.put("success",success);
            ret.put("error","请先登录！");
            return ret;
        }
        try {
            if(admin.getRole().getId().equals(1l)||admin.getRole().getId().equals(2l)){
                billService.truncate();
            }else{
                billService.delByAccount(admin.getId());
            }
            success = true;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
        }
        ret.put("success", success);
        return ret;
    }

    // 通过id删除
    @RequestMapping(value = "/truncateWrong", method = RequestMethod.GET)
    public JSONObject truncateWrong(HttpSession session) {
        JSONObject ret = new JSONObject();
        boolean success = false;
        AdminUser admin = (AdminUser)session.getAttribute("admin");
        if(admin == null){
            ret.put("success",success);
            ret.put("error","请先登录！");
            return ret;
        }
        try {
            wrongBillService.truncate();
            success = true;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
        }
        ret.put("success", success);
        return ret;
    }





    @RequestMapping(value = "/uploadExcel", method = RequestMethod.POST)
    public JSONObject uploadExcel(@RequestParam("file") MultipartFile file,HttpSession session) {
        JSONObject ret = new JSONObject();
        boolean success = false;
        AdminUser admin = (AdminUser)session.getAttribute("admin");
        if(admin == null){
            ret.put("success",success);
            ret.put("error","请先登录！");
            return ret;
        }
        Count statics = new Count();
        String curSheet = "";
        String curRow = "";
        try {
            InputStream ins =  file.getInputStream();
            Workbook wookbook = WorkbookFactory.create(ins);
            Date optime = new Date();
            for(int sheetIndex =0;sheetIndex<wookbook.getNumberOfSheets();sheetIndex++){
               Sheet sheet = wookbook.getSheetAt(sheetIndex);
                String companyName = sheet.getSheetName();

                curSheet = "Sheet页："+companyName;

                Row topRow = sheet.getRow(0);
                if(topRow == null ) continue;
                Iterator<Cell> iterator = topRow.cellIterator();
                Integer i_bussi_no = null,i_compu_no = null,i_bussi_amount = null,i_compu_amount = null,i_name = null,i_license_plate = null,i_company_name = null;
                while(iterator.hasNext()){
                    Cell cell = iterator.next();
                        String title = cell.getStringCellValue();
                    if(title.equals(BUSINESS_NO)){
                        i_bussi_no = cell.getColumnIndex();
                    }else if(title.equals(COMPULSORY_NO)){
                        i_compu_no = cell.getColumnIndex();
                    }else if(title.equals(BUSINESS_AMOUNT)||title.equals(BUSINESS_AMOUNT2)){
                        i_bussi_amount = cell.getColumnIndex();
                    }else if(title.equals(COMPULSORY_AMOUNT)||title.equals(COMPULSORY_AMOUNT2)){
                        i_compu_amount = cell.getColumnIndex();
                    }else if(title.equals(NAME)){
                        i_name = cell.getColumnIndex();
                    }else if(title.equals(LICENSE_PLATE)){
                        i_license_plate = cell.getColumnIndex();
                    }else if(title.equals(COMPANY_NAME)){
                        i_company_name = cell.getColumnIndex();
                    }

                }
                if(i_bussi_no == null||i_compu_no==null||i_bussi_amount==null||i_compu_amount==null||i_name==null||i_license_plate==null){
                    ret.put("success",success);
                    ret.put("error","总帐单格式与模板不一致,请确认!");
                    return ret;
                }
                for(int rowIndex =4,totalRow = sheet.getLastRowNum();rowIndex<=totalRow;rowIndex++) {
                    Row row = sheet.getRow(rowIndex);
                    if(row==null) continue;
                    String name = ExcelReader.getString(row.getCell(i_name));
                    String license_plate = ExcelReader.getString(row.getCell(i_license_plate));
                    String bussi_no =  ExcelReader.getString(row.getCell(i_bussi_no));
                    String compu_no =  ExcelReader.getString(row.getCell(i_compu_no));

                    String bussi_amount_str =  ExcelReader.getString(row.getCell(i_bussi_amount));
                    String compu_amount_str =  ExcelReader.getString(row.getCell(i_compu_amount));

                    curRow = "第"+rowIndex+"行："+BUSINESS_NO+"="+bussi_no+"，"+BUSINESS_AMOUNT+"="+bussi_amount_str+"，"+COMPULSORY_NO+"="+compu_no+"，"+COMPULSORY_AMOUNT+"="+compu_amount_str;
                    log.info(curRow);

                    Double bussi_amount = ExcelReader.getDouble(row.getCell(i_bussi_amount));
                    Double compu_amount =ExcelReader.getDouble(row.getCell(i_compu_amount));
                    if(i_company_name!=null){
                        companyName = ExcelReader.getString(row.getCell(i_company_name));
                    }

                    if(!StringUtils.isEmpty(bussi_no)){
                        if(bussi_no.indexOf("'")>-1){
                            System.err.println(bussi_no);
                        }
                        bussi_no = bussi_no.replaceAll("\\W","");
                        Bill bill = new Bill();
                        bill.setName(name);
                        bill.setLicensePlate(license_plate);
                        bill.setOptime(optime);
                        bill.setInsuranceNo(bussi_no);
                        bill.setAmount(bussi_amount);
                        bill.setCompany(companyName);
                        bill.setOp(admin.getId());
                        billService.findExactRec(bill,statics);
                    }
                    if(!StringUtils.isEmpty(compu_no)){
                        if(compu_no.indexOf("'")>-1){
                            System.err.println(compu_no);
                        }
                        compu_no = compu_no.replaceAll("\\W","");
                        Bill bill = new Bill();
                        bill.setName(name);
                        bill.setLicensePlate(license_plate);
                        bill.setOptime(optime);
                        bill.setInsuranceNo(compu_no);
                        bill.setAmount(compu_amount);
                        bill.setCompany(companyName);
                        bill.setOp(admin.getId());
                        billService.findExactRec(bill,statics);
                    }
                }
            }
            success = true;
        } catch (Exception e) {
            ret.put("error", e.getMessage()+"</br>错误位置："+curSheet+"，</br>"+curRow);
            ret.put("statics",statics);
            log.error(e.getMessage(),e);
        }
        ret.put("statics",statics);
        ret.put("success",success);
        return ret;
    }



    @RequestMapping(value = "/compareExcel", method = RequestMethod.POST)
    public JSONObject compareExcel(@RequestParam("file") MultipartFile file,HttpServletResponse resp) throws IOException{
        JSONObject ret = new JSONObject();
        InputStream ins = null;
        ServletOutputStream out = null;
        try {
            ins =  file.getInputStream();
            String oriName =file.getOriginalFilename();
            Workbook wookbook = WorkbookFactory.create(ins);
            billService.campare(wookbook);
            resp.setContentType("application/octet-stream");
            //通知浏览器下载文件而不是打开
            String fileName =  java.net.URLEncoder.encode(ExcelReader.replaceLast(oriName, ".", "_比对结果."), "UTF-8");
            resp.addHeader("Content-Disposition", "attachment; filename="+fileName);
            out = resp.getOutputStream();
            wookbook.write(out);
        } catch (Exception e) {
            ret.put("error",e.getMessage());
            log.error(e.getMessage(),e);
        }finally {
            if(ins!=null)   ins.close();
            if(out !=null)   out.close();
        }
        return ret;
    }

    @RequestMapping(value = "/uploadTotalBill", method = RequestMethod.POST)
    public JSONObject uploadTotalBill(HttpSession session ,@RequestParam("file") MultipartFile file)  throws IOException{
        JSONObject ret = new JSONObject();
        boolean success = false;
        InputStream ins = null;
        try {
            ins =  file.getInputStream();
            Workbook wookbook = WorkbookFactory.create(ins);
            for(int sheetIndex =0;sheetIndex<wookbook.getNumberOfSheets();sheetIndex++){
                Sheet sheet = wookbook.getSheetAt(sheetIndex);
                String companyName = sheet.getSheetName();
                int topRowIndex = sheet.getTopRow();
                // Row topRow = sheet.getRow(sheet.getTopRow());
                Row topRow = sheet.getRow(0);
                if(topRow == null ) continue;
                Iterator<Cell> iterator = topRow.cellIterator();
                Integer i_bussi_no = null,i_compu_no = null,i_bussi_amount = null,i_compu_amount = null,i_name = null,i_license_plate = null,i_company_name = null;
                while(iterator.hasNext()){
                    Cell cell = iterator.next();
                    String title = cell.getStringCellValue();
                    if(title.equals(BUSINESS_NO)){
                        i_bussi_no = cell.getColumnIndex();
                    }else if(title.equals(COMPULSORY_NO)){
                        i_compu_no = cell.getColumnIndex();
                    }else if(title.equals(BUSINESS_AMOUNT)||title.equals(BUSINESS_AMOUNT2)){
                        i_bussi_amount = cell.getColumnIndex();
                    }else if(title.equals(COMPULSORY_AMOUNT)||title.equals(COMPULSORY_AMOUNT2)){
                        i_compu_amount = cell.getColumnIndex();
                    }else if(title.equals(NAME)){
                        i_name = cell.getColumnIndex();
                    }else if(title.equals(LICENSE_PLATE)){
                        i_license_plate = cell.getColumnIndex();
                    }else if(title.equals(COMPANY_NAME)){
                        i_company_name = cell.getColumnIndex();
                    }
                }
                if(i_bussi_no == null||i_compu_no==null||i_bussi_amount==null||i_compu_amount==null||i_name==null||i_license_plate==null){
                    ret.put("success",success);
                    ret.put("error","总帐单格式与模板不一致,请确认!");
                    return ret;
                }
            }
            String APPLICATION_URL = session.getServletContext().getRealPath("/");
            File basePath = new File(APPLICATION_URL + TEMP_UPLOAD_PATH);
            if (!basePath.exists()) {
                basePath.mkdirs();
            }
            String tempPath = TEMP_UPLOAD_PATH+"/"+session.getId()+"_totalBill.xls";
            File f = new File(APPLICATION_URL+tempPath);
            if(f.exists()){
                f.delete();
            }
            file.transferTo(f);
            session.setAttribute("totalBill",APPLICATION_URL+tempPath);
            session.setAttribute("totalBillName",file.getOriginalFilename());
            ret.put("size",file.getSize() / 1024);
            ret.put("name", file.getOriginalFilename());
            ret.put("url",tempPath);
            success = true;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            ret.put("error", e.getMessage());
        }finally {
            if(ins!=null)   ins.close();
        }
        ret.put("success",success);
        return ret;
    }

    @RequestMapping(value = "/uploadSubBill", method = RequestMethod.POST)
    public JSONObject uploadSubBill(@RequestParam("file") MultipartFile file,HttpSession session) throws IOException{
        JSONObject ret = new JSONObject();
        boolean success = false;
        InputStream ins = null;
        try {
            ins =  file.getInputStream();
            String oriName =file.getOriginalFilename();
            Workbook workbook = WorkbookFactory.create(ins);
            Sheet sheet = workbook.getSheetAt(0);
           // Row topRow = sheet.getRow(sheet.getTopRow());
            Row topRow = sheet.getRow(0);
            Integer i_no = null,i_name = null,i_plate = null,i_amount = null;
            Iterator<Cell> iterator = topRow.cellIterator();
            while(iterator.hasNext()){
                Cell cell = iterator.next();
                String title = cell.getStringCellValue();
                if(title.indexOf("单")>-1 && i_no== null){;
                    i_no = cell.getColumnIndex();
                }else if(title.indexOf("人")>-1  && i_name== null){
                    i_name = cell.getColumnIndex();
                }else if(title.indexOf("牌")>-1  && i_plate== null){
                    i_plate = cell.getColumnIndex();
                }else if(title.indexOf("额")>-1  && i_amount== null){
                    i_amount = cell.getColumnIndex();
                }
            }
            if(i_no == null||i_name==null||i_plate==null||i_amount==null){
                ret.put("error","子帐单格式错误,请确认!");
            }else{
                String APPLICATION_URL = session.getServletContext().getRealPath("/");
                File basePath = new File(APPLICATION_URL + TEMP_UPLOAD_PATH);
                if (!basePath.exists()) {
                    basePath.mkdirs();
                }
                String tempPath = TEMP_UPLOAD_PATH+"/"+session.getId()+"_subBill.xls";
                File f = new File(APPLICATION_URL+tempPath);
                if(f.exists()){
                    f.delete();
                }
                file.transferTo(f);
                session.setAttribute("subBill",APPLICATION_URL+tempPath);
                session.setAttribute("subBillName",file.getOriginalFilename());
                ret.put("size",file.getSize() / 1024);
                ret.put("name", file.getOriginalFilename());
                ret.put("url",tempPath);
                success = true;
            }
        } catch (Exception e) {
            ret.put("error",e.getMessage());
            log.error(e.getMessage(),e);
        }finally {
            if(ins!=null)   ins.close();
        }
        ret.put("success",success);
        return ret;
    }

    @RequestMapping(value = "/getUploadedExcels")
    public JSONObject getUploadedExcels(HttpSession session) throws IOException{
        JSONObject ret = new JSONObject();
        ret.put("totalBillName",session.getAttribute("totalBillName"));
        ret.put("subBillName",session.getAttribute("subBillName"));
        return ret;
    }

    @RequestMapping(value = "/unFinished")
    public JSONObject unFinished(HttpSession session) throws IOException{
        JSONObject ret = new JSONObject();
        ret.put("unFinished",session.getAttribute("unFinished"));
        return ret;
    }

    //直接excel文件比较
    @RequestMapping(value = "/compareBills")
    public JSONObject compareBills(HttpSession session,HttpServletResponse resp) throws IOException{
        JSONObject ret = new JSONObject();
        ServletOutputStream out = null;
        try {
            File totalBill = new File(session.getAttribute("totalBill").toString());
            File subBill = new File(session.getAttribute("subBill").toString());
            if(totalBill ==null ||subBill == null){
                ret.put("error","上传的账单临时文件已过期,请重新上传！");
            }
            String oriName =session.getAttribute("totalBillName").toString();
            Workbook wookbook = billService.campareBill(totalBill,subBill,session);
            resp.setContentType("application/octet-stream");
            //通知浏览器下载文件而不是打开
            String fileName =  java.net.URLEncoder.encode(ExcelReader.replaceLast(oriName, ".", "_比对结果."), "UTF-8");
            resp.addHeader("Content-Disposition", "attachment; filename="+fileName);
            out = resp.getOutputStream();
            wookbook.write(out);
        } catch (Exception e) {
            ret.put("error",e.getMessage());
            log.error(e.getMessage(),e);
        }finally {
            if(out !=null)   out.close();
        }
        return ret;
    }

    //直接excel文件比较
    @RequestMapping(value = "/changeStatus")
    public JSONObject changeStatus(Long billId,Integer status) throws IOException{
        JSONObject ret = new JSONObject();
        boolean success = false;
        try {
            WrongBill bill = wrongBillService.get(billId);
            bill.setStatus(status);
            wrongBillService.update(bill);
            success = true;
        } catch (Exception e) {
            ret.put("error",e.getMessage());
            log.error(e.getMessage(),e);
        }
        ret.put("success",success);
        return ret;
    }
}
